﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Text;
using Cosmetic.DataAccess;
using Cosmetic.DataEntity;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Cosmetic.Logic
{

    public class Top50Manager
    {
        /// <summary>
        /// 会员推荐至TOP50的商品。
        /// </summary>
        /// <returns></returns>
        public static void InsertToTable(string productId)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdSelect = "select count(1) from [dbo].[Top50] where  [ProductId] = '" + productId + "'";
                SqlCommand comSelect = new SqlCommand(cmdSelect, con);
                object result = comSelect.ExecuteScalar();
                if (result != null && !string.IsNullOrEmpty(result.ToString()) && int.Parse(result.ToString()) > 0)
                {
                    string cmdStr = "  update [Top50] set [Sequence]= [Sequence]+1 where [ProductId] ='" + productId + "'";
                    SqlCommand updateCom = new SqlCommand(cmdStr, con);
                    updateCom.ExecuteNonQuery();
                }
                else
                {
                    string cmdStr = "INSERT INTO [dbo].[Top50] ([ProductId] ,[CatetoryId]  ,[Sequence]  ,[CreateTime])  VALUES  ('" + productId + "'  ,newid()  ,1  ,getdate())";
                    SqlCommand com = new SqlCommand(cmdStr, con);
                    com.ExecuteNonQuery();
                }

            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 查寻所有推荐至TOP50的商品。
        /// </summary>
        /// <returns></returns>
        public static DataSet GetAllProduct()
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select ProductId ,[Name],PictureUrl,网络零售价 as [price],Views,PublishDate from  ProductInfo where ProductId in ( select ProductId from dbo.[Top50] )";

                SqlCommand com = new SqlCommand(cmdStr, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                return ds;
            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>
        /// 查寻所有推荐至TOP50的商品， 按推荐次数排序。
        /// </summary>
        /// <returns></returns>
        public static DataSet GetAllProductOrderBySequence()
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select pro.[Name] , pro.[网络零售价] as [price] , tp.[Sequence] from ProductInfo as pro inner join Top50 as tp on pro.ProductId = tp.ProductId  order by Sequence desc";

                SqlCommand com = new SqlCommand(cmdStr, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                return ds;
            }
            finally
            {
                con.Close();
            }
        }

    }//end class
}
